﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;


namespace StateGrid.MDBservice
{
    delegate object FieldAdjustHandler(string key, object value);
    delegate void RecordAdjustHandler(IDictionary<string, object> fields);
    delegate bool FieldCheckHandler(string key, object value, out string message);
    delegate bool RecordCheckHandler(IDictionary<string, object> fields, out string message);
    delegate object FieldSubmitHandler(string key, object value);
    delegate object RecordSubmitHandler(IDictionary<string, object> fields);
    
    public class ColumnInfo
    {
        public enum ColumnType
        {
            STRING,
            INTEGER,
            BOOLEAN,
            DATE
        }


        public static ColumnType StringToType(string s)
        {
            switch (s)
            {
                case "nvarchar":
                case "130":
                    return ColumnType.STRING;
                case "int":
                case "2":
                case "3":
                    return ColumnType.INTEGER;
                case "bit":
                case "11":
                    return ColumnType.BOOLEAN;
                case "datetime":
                case "7":
                    return ColumnType.DATE;
                default:
                    return ColumnType.STRING;
            }
        }

        public string LocalName;
        public string RemoteName;

        public ColumnType Type;
        public int? Length;
        public bool IsNullable;
    }

    public class DataService
    {
        static public string[] GetLocalTableList(string localFile)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + localFile);
            con.Open();

            var query = from row in con.GetSchema("Tables", new string[] { null, null, null, "TABLE" }).AsEnumerable()
                        select row["TABLE_NAME"].ToString();
            string[] result = query.ToArray();

            con.Close();
            return result;
        }

        protected virtual string MainTable
        {
            get
            {
                return "GJBZ_PM";
            }
        }

        /// <summary>
        /// 定义用来连接关系表的主键
        /// </summary>
        protected virtual string RelationKey
        {
            get
            {
                return "bzwh";
            }
        }

        /// <summary>
        /// 关系域，即将关系表中的关系体现在该域中
        /// </summary>
        protected virtual string RelationField
        {
            get
            {
                return "dtbz";
            }
        }

        /// <summary>
        /// 反向关系域
        /// </summary>
        protected virtual string ReverseRelationField
        {
            get
            {
                return "bdtbz";
            }
        }

        public string ErrorLog = "";
        public string WarnningLog = "";

        // 本地文件
        private string localFile;
        private string localTable;
        private OleDbConnection localConnection;

        // 远程数据库
        private string remoteConnectionString = @"Data Source=127.0.0.1;DataBase=StateGrid;User Id=sa;Password=sg_123";
        private SqlConnection remoteConnection;

        // 描述数据库表结构
        private Dictionary<string, string> aliasRules = new Dictionary<string,string>();
        private List<string[]> uniqueRules = new List<string[]>();
        
        // 列信息
        private List<ColumnInfo> columnList = new List<ColumnInfo>();
        private Dictionary<string, ColumnInfo> columnTable = new Dictionary<string,ColumnInfo>();

        protected void ClearLog()
        {
            ErrorLog = "";
            WarnningLog = "";
        }

        protected void LogError(string s)
        {
            ErrorLog += s + "\n";
        }

        protected void LogWarnning(string s)
        {
            WarnningLog += s + "\n";
        }

        public void InitRemoteConnection()
        {
            remoteConnection = new SqlConnection(remoteConnectionString);
            remoteConnection.Open();    
        }

        public DataService()
        {
            InitRemoteConnection();
            RegisterSchemaRules();
        }

        public bool ConstructColumnTable()
        {
            bool error = false;

            columnList.Clear();
            columnTable.Clear();

            DataTable remoteSchema = remoteConnection.GetSchema("Columns", new string[] { null, null, this.MainTable });
            Dictionary<string, bool> mark = new Dictionary<string, bool>();
            foreach (DataRow row in remoteSchema.Rows)
            {
                ColumnInfo c = new ColumnInfo
                {
                    RemoteName = row["column_name"].ToString(),
                    Type = ColumnInfo.StringToType(row["data_type"].ToString()),
                    Length = row["character_maximum_length"] as int?,
                    IsNullable = row["is_nullable"].ToString() == "YES" ? true : false
                };

                columnTable[c.RemoteName] = c;
                mark[c.RemoteName] = false;
            }

            
            DataTable localSchema = localConnection.GetSchema("Columns", new string[] { null, null, localTable });
            foreach (DataRow row in localSchema.Rows)
            {
                string localName = row["column_name"] as string;

                if (!(aliasRules.ContainsKey(localName) && columnTable.ContainsKey(aliasRules[localName])))
                    continue;
                ColumnInfo column = columnTable[aliasRules[localName]];
                if (mark[column.RemoteName])
                {
                    // 已经出现过的列，忽略
                    continue;
                }

                if (column.Type != ColumnInfo.StringToType(row["data_type"].ToString()))
                {
                    LogError(String.Format("列'{0}'和数据库中的类型不匹配", localName));
                    error = true;
                }
                else
                {
                    column.LocalName = localName;
                    columnTable[localName] = column;
                    columnList.Add(column);
                    mark[column.RemoteName] = true;
                }
            }

            foreach (string c in mark.Keys)
            {
                if (mark[c] == false && !columnTable[c].IsNullable)
                {
                    error = true;
                    LogError(String.Format("缺少列'{0}'", c));
                }
            }

            return !error;
        }
        

        public bool OpenLocalFile(string localFile, string localTable)
        {
            ClearLog();

            this.localFile = localFile;
            this.localTable = localTable;

            this.localConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + localFile);
            localConnection.Open();

            if (!ConstructColumnTable())
                return false;

            TemporaryDataFile file = new TemporaryDataFile(localFile, columnList);
            file.CreateMainTable("test");
            file.RenameTable("test", "待更正表");
            file.CreateIndex("待更正表", "test", "标准文号");

            OleDbCommand cmd = new OleDbCommand(String.Format("select * from [{0}]", localTable), localConnection);
            OleDbDataReader reader = cmd.ExecuteReader();

            reader.Read();
            List<string> fields = new List<string>();
            List<object> values = new List<object>();
            foreach (ColumnInfo column in columnList)
            {
                fields.Add(column.LocalName);
                values.Add(reader[column.LocalName]);
            }
            file.InsertRecord("待更正表", fields, values);

            return true;
        }

        protected void RegisterAliasRule(string localName, string remoteName)
        {
            this.aliasRules.Add(localName, remoteName);
        }

        protected void RegisterUniqueRule(params string[] fields)
        {
            this.uniqueRules.Add(fields);
        }

        protected virtual void RegisterSchemaRules()
        {
            RegisterAliasRule("分类编码", "flbm");
            RegisterAliasRule("标准文号", "bzwh");
            RegisterAliasRule("文件名", "wjm");
            RegisterAliasRule("ICS分类", "iscfl");
            RegisterAliasRule("GB分类", "gbfl");
            RegisterAliasRule("版本性质", "bbxz");
            RegisterAliasRule("中文名称", "zwmc");
            RegisterAliasRule("外文名称", "wwmc");
            RegisterAliasRule("发布日期", "fbrq");
            RegisterAliasRule("实施日期", "ssrq");
            RegisterAliasRule("采用关系", "cygx");
            RegisterAliasRule("页数", "ys");
            RegisterAliasRule("作废", "zf");
            RegisterAliasRule("代替标准", "dtbz");
            RegisterAliasRule("被代替标准", "bdtbz");
            RegisterAliasRule("作废年份", "zfnf");
            RegisterAliasRule("中文主题词", "zwztc");
            RegisterAliasRule("外文主题词", "wwztc");
            RegisterAliasRule("起草单位", "qcdw");
            RegisterAliasRule("发布单位", "fbdw");
            RegisterAliasRule("批准单位", "pzdw");
            RegisterAliasRule("标准代号含义", "bzdhhy");
            RegisterAliasRule("备注", "bz");
            RegisterAliasRule("更新项", "gxx");
            RegisterAliasRule("更新日期", "gxrq");
            RegisterAliasRule("登陆人", "dlr");

            RegisterUniqueRule("bzwh");
        }
    }
}
